package com.etc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.etc.dao.ManagerDao;
import com.etc.entity.Manager;
import com.etc.entity.Person;
import com.etc.entity.PageModel;
import com.etc.util.JdbcUtil;

public class ManagerDaoImpl implements ManagerDao {

	/**
	 * 通过帐号查找用户的信息
	 */
	public List<Person>  findByAccount(String account) {
		List<Person> list=new ArrayList<Person>();
		Connection conn=JdbcUtil.getConn();
		String sql="select id,account,name,balance from user where account like ? ";
		ResultSet rs=null;
		PreparedStatement ps=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, "%"+account+"%");
			rs = ps.executeQuery();
			while(rs.next())
			{
				Person p=new Person();
				p.setId(rs.getInt("id"));
				p.setName(rs.getString("name"));
				p.setAccount(rs.getString("account"));
				p.setBalance(rs.getDouble("balance"));
				list.add(p);
			}
			JdbcUtil.closeAll(null, ps, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	/**
	 * 查看所有用户信息
	 */

	public List<Person> queryAll() {
		
		List<Person> list=new ArrayList<Person>();
		Connection conn=JdbcUtil.getConn();
		String sql="select * from user";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Person p=new Person();
				p.setId(rs.getInt("id"));
				p.setName(rs.getString("name"));
				p.setAccount(rs.getString("account"));
				p.setBalance(rs.getDouble("balance"));
				list.add(p);
			}
			JdbcUtil.closeAll(null, ps, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 验证 管理员账号密码
	 */
	public boolean checkAccount(String maccount,String mpwd) {
		// TODO Auto-generated method stub
		Connection conn=JdbcUtil.getConn();
		String sql="select maccount,mpwd from manager";
		ResultSet rs = null;
		String account="";
		String pwd="";
		boolean tag=false;
		try {
			rs=JdbcUtil.doQuery(conn, sql, null);
			if(rs.next()){
				account=rs.getString("maccount");
				pwd=rs.getString("mpwd");
				if(maccount.equals(account)&&mpwd.equals(pwd))
					tag=true;
				else
					tag=false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tag;
	}

	@Override
	public void modifyBalance(int id, double balance) {
		// TODO Auto-generated method stub
		Connection conn=JdbcUtil.getConn();
		String sql="update user set balance=? where id=?";
		PreparedStatement ps = null;
		try {
			ps=conn.prepareStatement(sql);
			JdbcUtil.doUpate(conn, sql, balance,id);
			JdbcUtil.closeAll(null, ps, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	/**
	 * 删除用户
	 */
	public void removeUser(int id){
		Connection conn=JdbcUtil.getConn();
		String sql="delete from user where id=?";
		PreparedStatement ps = null;
		try {
			ps=conn.prepareStatement(sql);
			JdbcUtil.doUpate(conn, sql, id);
			JdbcUtil.closeAll(null, ps, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	};
	/**
	 * 分页
	 */
	public List<Person> getPersonListByPage(int currentPage, int pageSize) {
		// 获取连接
 		Connection conn = JdbcUtil.getConn();
		int startIndex = (currentPage - 1) * pageSize;
		// 创建预编译语句对象，设置动态参数，执行查询
		ResultSet rSet = null;
		StringBuilder sb = new StringBuilder(100);
		sb.append("select * from user ");
		sb.append(" limit ? , ? ");
		rSet = JdbcUtil.doQuery(conn, sb.toString(), startIndex, pageSize);
		// 遍历结果集
		List<Person> PersonList = new ArrayList<Person>();// 定义集合来存储新闻列表
		try {
			while (rSet.next()) {
				int id = rSet.getInt("id");
				String account = rSet.getString("account");
				double balance = rSet.getDouble("balance");
				String name = rSet.getString("name");
				Person person = new Person(id, account, "", name, balance, "", "", 0);
				PersonList.add(person);
			}
			// 关闭连接
			JdbcUtil.closeAll(rSet, rSet.getStatement(), conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		return PersonList;
	}

	@Override
	public int getUserCount() {
		Connection conn=JdbcUtil.getConn();
		String sql="select count(id) as count from user ";
		ResultSet rs=null;
		int count=0;
		try {
			rs=JdbcUtil.doQuery(conn, sql, null);
			if(rs.next()){
			count=rs.getInt("count");
			}
			JdbcUtil.closeAll(rs, rs.getStatement(), conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return count;
	}

	@Override
	public PageModel<Person> getPageModelListByPage(int currentPage,
			int pageSize, int totalRecord) {
		List<Person> personList = new ArrayList<Person>();
		PageModel<Person> pageModel = new PageModel<Person>();
		ManagerDao dao=new ManagerDaoImpl();
		if (totalRecord == -1) {// 点搜索的时候，需要去获取总记录数，点分页信息的时候，不需要去重新获取
			totalRecord = dao.getUserCount();
		}
		if (totalRecord > 0) {
			personList = dao.getPersonListByPage(currentPage, pageSize);
		}
		pageModel.setCurrentPage(currentPage);
		pageModel.setPageSize(pageSize);
		pageModel.setTotalRecord(totalRecord);
		pageModel.setList(personList);
		return pageModel;
		
		
	}
}
